﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace API.Models.Repository
{
    public class ITEMS_MAPPING_Repository : GenericRepository<PartnerDbContext, ITEMS_MAPPING>
    {
        public IEnumerable<ITEM_COMPANY> GetUnbinding()
        {
            var sql = @"
            SELECT
	            DISTINCT COMPANY_GUID, COMPANY_NAME, ITEM_GUID, ITEM_NAME
            FROM 
	            WH_INVOICE_LINES
	            LEFT JOIN WH_ITEMS ON WH_INVOICE_LINES.ITEM_GUID = WH_ITEMS.GUID_RECORD
	            LEFT JOIN WH_COMPANIES ON WH_ITEMS.COMPANY_GUID = WH_COMPANIES.GUID_RECORD
            WHERE
	            EXISTS (SELECT 1 FROM WH_INVOICES 
		            WHERE 1 = 1
			            AND WH_INVOICES.GUID_RECORD = WH_INVOICE_LINES.INVOICE_GUID 
			            AND CUSTOMER_MAPPED_GUID = {0})
	            AND (ITEM_MAPPED_GUID IS NULL
		            OR ITEM_MAPPED_GUID = '00000000-0000-0000-0000-000000000000')";

            return this.Context.Database.SqlQuery<ITEM_COMPANY>(sql, this.session.COMPANY_GUID);
        }
        
        /// <summary>
        /// Получить соответствие для заданного товара контрагента
        /// </summary>
        /// <param name="ITEM_GUID">Код товара контрагента</param>
        /// <returns></returns>
        public ITEM_COMPANY_MAP GetMapping(Guid ITEM_GUID)
        {
            /// Опасный кусок!
            var ITEM = _entities.WH_ITEMS
                .Include("COMPANY")
                .SingleOrDefault(r => r.GUID_RECORD == ITEM_GUID);

            var sql = @"
            SELECT 
                [{0}] MAP_ITEM_GUID,
                ITEM_NAME MAP_ITEM_NAME
            FROM WH_ITEMS_MAPPING
	            LEFT JOIN WH_ITEMS ON [{0}] = WH_ITEMS.GUID_RECORD
            WHERE
                1 = 1
                AND [{1}] = '{2}'
            ";

            sql = String.Format(sql, this.session.COMPANY_GUID, ITEM.COMPANY_GUID, ITEM_GUID);

            var data = this.Context.Database.SqlQuery<ITEM_COMPANY_MAP>(sql).SingleOrDefault();

            if (data == null)
                return new ITEM_COMPANY_MAP
                {
                    ITEM_GUID = ITEM_GUID,
                    ITEM_NAME = ITEM.ITEM_NAME,
                    COMPANY_GUID = ITEM.COMPANY_GUID,
                    COMPANY_NAME = ITEM.COMPANY.COMPANY_NAME
                };
                //throw new Exception("Соответствие для товара с идентификатором " + ITEM_GUID.ToString() + " не найдено");

            data.ITEM_GUID = ITEM_GUID;
            data.ITEM_NAME = ITEM.ITEM_NAME;
            data.COMPANY_GUID = ITEM.COMPANY_GUID;
            data.COMPANY_NAME = ITEM.COMPANY.COMPANY_NAME;

            return data;

        }

        public IEnumerable<ITEMS_MAPPING> GetMapping(Guid COMPANY_GUID, Guid ITEM_GUID)
        {
            var sec_companies_access_repository = new SEC_COMPANIES_ACCESS_Repository();
            var allow_items_repository = new ALLOW_ITEMS_Repository();

            sec_companies_access_repository.SetSession(this.session);
            allow_items_repository.SetSession(this.session);

            var access_companies = sec_companies_access_repository
                .Find(r => r.IS_ALLOWED)
                .ToList();

            var sql = @"
            SELECT 			
	            CAST(MAP.COMPANY_GUID AS uniqueidentifier) GUID_RECORD,
	            CAST(MAP.COMPANY_GUID AS uniqueidentifier) COMPANY_GUID,
	            MAP.ITEM_GUID
            FROM   
                (SELECT 
		            GUID_RECORD,
		            {2}
	            FROM WH_ITEMS_MAPPING
	            WHERE
		            [{0}] = '{1}') p  
                UNPIVOT  
		            (ITEM_GUID FOR COMPANY_GUID IN (
		            {2})
		            )  AS MAP";

            sql = String.Format(sql,
                COMPANY_GUID.ToString(), ITEM_GUID.ToString(),
                String.Join(", ", access_companies.Select(r =>
                    String.Format("[{0}]", r.ALLOW_TO_GUID.ToString()))));

            var data = this.Context.Database.SqlQuery<ITEMS_MAPPING>(sql);

            data = data.Select(r =>
            {
                var item = allow_items_repository.Find(n => 
                    n.COMPANY_GUID == r.COMPANY_GUID &&
                    n.GUID_RECORD == r.ITEM_GUID).SingleOrDefault();

                if (item != null)
                    r.ITEM = item;

                return r;
            });

            return data;
        }

        public void SetMapping(Guid ITEM_GUID, Guid SUPPLIER_ITEM_GUID)
        {
            /// Опасный кусок!
            var LEFT_ITEM = _entities.WH_ITEMS
                .SingleOrDefault(r => r.GUID_RECORD == ITEM_GUID);

            var RIGHT_ITEM = _entities.WH_ITEMS
                .SingleOrDefault(r => r.GUID_RECORD == SUPPLIER_ITEM_GUID);

            var COMPANIES = _entities.WH_COMPANIES
                .Where(r => !r.OWNER_GUID.HasValue)
                .ToList();

            var sql = @"
            DECLARE
	            @LEFT_ITEM_GUID		uniqueidentifier,
	            @RIGHT_ITEM_GUID	uniqueidentifier,
	            @LEFT_GUID_RECORD	uniqueidentifier,
	            @RIGHT_GUID_RECORD	uniqueidentifier;

            SET @LEFT_ITEM_GUID = '{0}';
            SET @RIGHT_ITEM_GUID = '{1}';

            SELECT
	            @LEFT_GUID_RECORD = GUID_RECORD
            FROM 
	            WH_ITEMS_MAPPING
            WHERE 
	            [{2}] = @LEFT_ITEM_GUID;

            SELECT
	            @RIGHT_GUID_RECORD = GUID_RECORD
            FROM 
	            WH_ITEMS_MAPPING
            WHERE 
	            [{3}] = @RIGHT_ITEM_GUID;

            IF @LEFT_GUID_RECORD = @RIGHT_GUID_RECORD BEGIN
	            -- Ничего не делаем
	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NULL AND @RIGHT_GUID_RECORD IS NULL BEGIN

	            INSERT INTO WH_ITEMS_MAPPING ([{2}], [{3}])
	            VALUES (@LEFT_ITEM_GUID, @RIGHT_ITEM_GUID);

	            SELECT 'INSERT NEW RECORD';
	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NOT NULL AND @RIGHT_GUID_RECORD IS NOT NULL BEGIN

	            UPDATE WH_ITEMS_MAPPING SET
		            {4}
	            WHERE
		            GUID_RECORD = @LEFT_GUID_RECORD;
	
	            DELETE WH_ITEMS_MAPPING	WHERE
		            GUID_RECORD = @RIGHT_GUID_RECORD;

	            SELECT 'UPDATE RECORD AND DELETE';
	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NOT NULL AND @RIGHT_GUID_RECORD IS NULL BEGIN
	
	            UPDATE WH_ITEMS_MAPPING SET
		            [{3}] = @RIGHT_ITEM_GUID
	            WHERE
		            GUID_RECORD = @LEFT_GUID_RECORD;

	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NULL AND @RIGHT_GUID_RECORD IS NOT NULL BEGIN

	            UPDATE WH_ITEMS_MAPPING SET
		            [{2}] = @LEFT_ITEM_GUID
	            WHERE
		            GUID_RECORD = @RIGHT_GUID_RECORD;

	            RETURN;
            END;";

            sql = String.Format(sql,
                LEFT_ITEM.GUID_RECORD, RIGHT_ITEM.GUID_RECORD,
                LEFT_ITEM.COMPANY_GUID, RIGHT_ITEM.COMPANY_GUID,
                String.Join(", ", COMPANIES.Select(r => String.Format(
                    "[{0}] = ISNULL([{0}], (SELECT [{0}] FROM WH_ITEMS_MAPPING WHERE GUID_RECORD = @RIGHT_GUID_RECORD))", 
                    r.GUID_RECORD.ToString()))));

            this.Context.Database.ExecuteSqlCommand(sql);

            /// Перекодировка связанных с товаром документов
            var outgoing_invoices_repository = new OutgoingInvoices_Repository();
            var incoming_invoices_repository = new IncomingInvoices_Repository();

            var outgoing_invoices = outgoing_invoices_repository
                .Find(r => r.LINES.Any(n => n.ITEM_GUID == ITEM_GUID || n.ITEM_GUID == SUPPLIER_ITEM_GUID))
                .ToList();

            var incoming_invoices = incoming_invoices_repository
                .Find(r => r.LINES.Any(n => n.ITEM_GUID == ITEM_GUID || n.ITEM_GUID == SUPPLIER_ITEM_GUID))
                .ToList();

            foreach (var r in outgoing_invoices)
                outgoing_invoices_repository.Recode(r.GUID_RECORD);

            foreach (var r in incoming_invoices)
                outgoing_invoices_repository.Recode(r.GUID_RECORD);
        }
    }
}